﻿using System;
using System.Collections.Generic;
using System.Linq;
using System.Text;
using System.Threading.Tasks;
using System.Data.SqlClient;
using Models;

namespace DAL
{
    public class ConsumptionAnalysisService
    {

        public int HaveTab( string TabNam ) {
            string sql = string.Format("select count(1) from ConsumptionAnalysis{0} ", TabNam);
            try {
                SqlConnection conn = ConnPoll.GetConn();
                return Convert.ToInt32(SQLHelper.ExecuteScalar(conn, System.Data.CommandType.Text, sql));
            } catch (Exception) {
                return -1;
            }
        }


        #region 创建消费分析表
        /// <summary>
        /// 创建消费分析表
        /// </summary>
        /// <param name="UserName">用户名</param>
        /// <returns></returns>
        public void CreateConsumptionAnalysis(string UserName)
        {
            SqlConnection conn = ConnPoll.GetConn();
            try
            {
                ConnPoll.Open();
                //表名+用户名的形式建表
                string tbna = " ConsumptionAnalysis" + UserName;
                string sql = " use bds249611391_db "
                                 + " create table " + tbna
                                 + " ("
                                 + " CAnalysisId int not null identity(1,1),"//主键Id
                                 + " FriendName nvarchar(400) not null,"//好友名
                                 + " CStandard nvarchar(400) not null,"//消费标准
                                 + " CHabit nvarchar(400) not null,"//消费习惯
                                 + " BrandOrientation nvarchar(500) not null,"//化妆品品牌倾向
                                 + " BrandOName nvarchar(500) not null,"//最近购买的化妆品
                                 + " SkincareBrand nvarchar(500) not null,"//护肤品品牌倾向
                                 + " BrandSName nvarchar(500) not null,"//最近购买的护肤品
                                 + " ClothingBrandTendency nvarchar(500) not null,"//穿衣品牌倾向
                                 + " RecentlyPurchasedClothes nvarchar(500) not null,"//最近购买的衣服
                                 + " JewelryBrandTendency nvarchar(500) not null,"//饰品品牌倾向
                                 + " RecentlyPurchasedJewelry nvarchar(500) not null,"//最近购买的饰品
                                 + " ComprehensiveAnalysis nvarchar(2000) not null"//综合分析
                                 + " ) "
                                 + " use bds249611391_db"
                                 + " alter table " + tbna
                                 + " add constraint PK_CAnalysisId  primary key (CAnalysisId)";
                SqlCommand com = new SqlCommand(sql, conn);
                com.ExecuteNonQuery();
                com=null;
                conn.Close();
            }
            catch (Exception)
            {

            }
        }
        #endregion

        #region 新增信息
        /// <summary>
        /// 新增信息
        /// </summary>
        /// <param name="ca">消费分析表类</param>
        /// <param name="UserName">用户名</param>
        /// <returns></returns>
        public int Insert(ConsumptionAnalysis ca, string UserName)
        {
            SqlConnection conn = ConnPoll.GetConn();
            string sql = "insert into ConsumptionAnalysis" + UserName + " (FriendName,CStandard,CHabit,BrandOrientation,BrandOName,SkincareBrand,BrandSName,ClothingBrandTendency,RecentlyPurchasedClothes,JewelryBrandTendency,RecentlyPurchasedJewelry,ComprehensiveAnalysis)"
                + " values (@FriendName,@CStandard,@CHabit,@BrandOrientation,@BrandOName,@SkincareBrand,@BrandSName,@ClothingBrandTendency,@RecentlyPurchasedClothes,@JewelryBrandTendency,@RecentlyPurchasedJewelry,@ComprehensiveAnalysis)";
            SqlParameter[] pars = new SqlParameter[]
            {
                new SqlParameter("@FriendName",ca.FriendName), 
                new SqlParameter("@CStandard",ca.CStandard),
                new SqlParameter("@CHabit",ca.CHabit),
                new SqlParameter("@BrandOrientation",ca.BrandOrientation),
                new SqlParameter("@BrandOName",ca.BrandOName),
                new SqlParameter("@SkincareBrand",ca.SkincareBrand),
                new SqlParameter("@BrandSName",ca.BrandSName),
                new SqlParameter("@ClothingBrandTendency",ca.ClothingBrandTendency),
                new SqlParameter("@RecentlyPurchasedClothes",ca.RecentlyPurchasedClothes),
                new SqlParameter("@JewelryBrandTendency",ca.JewelryBrandTendency),
                new SqlParameter("@RecentlyPurchasedJewelry",ca.RecentlyPurchasedJewelry),
                new SqlParameter("@ComprehensiveAnalysis",ca.ComprehensiveAnalysis)
            };
            return SQLHelper.ExecuteNonQuery(conn, System.Data.CommandType.Text,sql,pars);
        }
        #endregion

        #region 删除信息
        /// <summary>
        /// 删除信息
        /// </summary>
        /// <param name="ca">消费分析表类</param>
        /// <param name="UserName">用户名</param>
        /// <returns></returns>
        public int Delete(int i, string UserName)
        {
            SqlConnection conn = ConnPoll.GetConn();
            string sql = "delete ConsumptionAnalysis" + UserName + " where CAnalysisId=@i";
            return SQLHelper.ExecuteNonQuery(conn, System.Data.CommandType.Text,sql,new SqlParameter("@i",i));
        }
        #endregion

        #region 修改信息
        /// <summary>
        /// 修改信息
        /// </summary>
        /// <param name="ca"></param>
        /// <param name="UserName"></param>
        /// <returns></returns>
        public int Set(ConsumptionAnalysis ca, string UserName,string FriendName)
        {
            SqlConnection conn = ConnPoll.GetConn();
            string sql = "update ConsumptionAnalysis" + UserName
                + " set "
                + " CStandard=@CStandard,"
                + " CHabit=@CHabit,"
                + " BrandOrientation=@BrandOrientation,"
                + " BrandOName=@BrandOName,"
                + " SkincareBrand=@SkincareBrand,"
                + " BrandSName=@BrandSName,"
                + " ClothingBrandTendency=@ClothingBrandTendency,"
                + " RecentlyPurchasedClothes=@RecentlyPurchasedClothes,"
                + " JewelryBrandTendency=@JewelryBrandTendency,"
                + " RecentlyPurchasedJewelry=@RecentlyPurchasedJewelry,"
                + " ComprehensiveAnalysis=@ComprehensiveAnalysis"
                + " where FriendName=@FriendNames";
            SqlParameter[] pars = new SqlParameter[]
            {
                new SqlParameter("@CStandard",ca.CStandard),
                new SqlParameter("@CHabit",ca.CHabit),
                new SqlParameter("@BrandOrientation",ca.BrandOrientation),
                new SqlParameter("@BrandOName",ca.BrandOName),
                new SqlParameter("@SkincareBrand",ca.SkincareBrand),
                new SqlParameter("@BrandSName",ca.BrandSName),
                new SqlParameter("@ClothingBrandTendency",ca.ClothingBrandTendency),
                new SqlParameter("@RecentlyPurchasedClothes",ca.RecentlyPurchasedClothes),
                new SqlParameter("@JewelryBrandTendency",ca.JewelryBrandTendency),
                new SqlParameter("@RecentlyPurchasedJewelry",ca.RecentlyPurchasedJewelry),
                new SqlParameter("@ComprehensiveAnalysis",ca.ComprehensiveAnalysis),
                new SqlParameter("@FriendNames",FriendName)
            };
            return SQLHelper.ExecuteNonQuery(conn, System.Data.CommandType.Text,sql,pars);
        }
        #endregion

        #region 查询信息
        /// <summary>
        /// 查询信息
        /// </summary>
        /// <param name="ca"></param>
        /// <param name="UserName"></param>
        /// <returns></returns>
        public int SearchAll( string UserName,string FriendName)
        {
            SqlConnection conn = ConnPoll.GetConn();
            string sql = "select Count(1) from ConsumptionAnalysis"+ UserName +" where FriendName = '" + FriendName+"'";
            try
            {
                return Convert.ToInt32(SQLHelper.ExecuteScalar(conn,System.Data.CommandType.Text,sql));
            }
            catch (Exception)
            {
                return -1;
            }
            
        }
        #endregion

       public ConsumptionAnalysis Show(string UserName, string FriendName)
        {
            SqlConnection conn = ConnPoll.GetConn();
            string sql = string.Format("select * from ConsumptionAnalysis{0} where FriendName = '{1}'", UserName, FriendName);
            ConsumptionAnalysis ca = new ConsumptionAnalysis();
            SqlDataReader dr = SQLHelper.ExecuteReader(conn, System.Data.CommandType.Text,sql);
            while (dr.Read())
            {
                ca.CAnalysisId = Convert.ToInt32(dr["CAnalysisId"]);
                ca.CStandard = dr["CStandard"].ToString().Trim();
                ca.CHabit = dr["CHabit"].ToString().Trim();
                ca.BrandOrientation = dr["BrandOrientation"].ToString().Trim();
                ca.BrandOName = dr["BrandOName"].ToString().Trim();
                ca.SkincareBrand = dr["SkincareBrand"].ToString().Trim();
                ca.BrandSName = dr["BrandSName"].ToString().Trim();
                ca.ClothingBrandTendency = dr["ClothingBrandTendency"].ToString().Trim();
                ca.RecentlyPurchasedClothes = dr["RecentlyPurchasedClothes"].ToString().Trim();
                ca.JewelryBrandTendency = dr["JewelryBrandTendency"].ToString().Trim();
                ca.RecentlyPurchasedJewelry = dr["RecentlyPurchasedJewelry"].ToString().Trim();
                ca.ComprehensiveAnalysis = dr["ComprehensiveAnalysis"].ToString().Trim();
            }
            dr.Close();
            return ca;
        }

    }
}
